import cn.hutool.core.util.StrUtil;
import com.infore.exchange.main.utils.SqlUtils;
import com.infore.metadata.core.enums.DatabaseEnum;
import com.infore.metadata.core.model.SimpleDataSource;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class HiveTableGenerate {

    public static void main(String[] args) {
        originalRowToColumn();
    }

    /**
     * 行转列-实时表
     */
    public static void originalRowToColumn(){

        String[] tables={"dwd.t_row_data_air","dwd.t_row_data_water"}; //,"dwd.t_row_data_pollution"
        String[] comments={"空气数据行转列","水数据行转列"};//,"污染源数据行转列"
        String[] projectTypeCodes={"'APT-0001','APT-0002'","'WPT-0001'"};
        for (int i = 0; i < tables.length; i++) {
            SimpleDataSource simpleDataSource = new SimpleDataSource(DatabaseEnum.MYSQL,
                    "jdbc:mysql://10.54.1.82:51096/db_envdatacenter?characterEncoding=UTF-8&useInformationSchema=true",
                    "root",
                    "Infore-cs@2022"
            );
            String table = tables[i];
            String comment = comments[i];
            String projectTypeCode = projectTypeCodes[i];
            StringBuilder sb = new StringBuilder("--{}\n");
            sb.append("CREATE TABLE {} ( \n");
            sb.append("station_code varchar(100)   COMMENT '站点编号（关联站点表）'\n");
            sb.append("`monitor_time` TIMESTAMP  COMMENT '监测时间',\n");
            String sql = "select project_code,project_chemical,project_name from t_bas_project where project_type_code in ("+projectTypeCode+") order by project_code desc";
            List<Map<String, Object>> list = SqlUtils.executeQuerySql(simpleDataSource, sql);

            for (Map<String, Object> map:list) {
                Object projectCode = map.get("project_code");
                Object projectName = map.get("project_name");
                System.out.println(projectCode+"\t"+projectName);
                sb.append("`"+projectCode+"` DECIMAL(12, 8)   COMMENT '"+projectName+"',\n");
            }
            sb.append(") COMMENT = '{}' \n");
            sb.append("partitioned by (monitor_date varchar(16)  COMMENT '日期分区，格式 YYYY-mm-dd') \n");
            sb.append("ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' \n");
            sb.append("STORED AS ORC;");
            String creatTableSql = StrUtil.format(sb.toString(),comment,table,comment) ;
            System.out.println(creatTableSql);
            try {
                simpleDataSource.closeConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}
